#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_course_user_watch_course_td="
insert overwrite table ${APP}.dws_course_user_watch_course_td partition (dt = '$do_date')
select distinct nvl(new.user_id,old.user_id),
       nvl(new.course_id,old.course_id),
       nvl(new.course_name,old.course_name),
       nvl(new.subject_id,old.subject_id),
       nvl(new.subject_name,old.subject_name),
       nvl(new.category_id,old.category_id),
       nvl(new.category_name,old.category_name),
       if(new.user_id is not null and old.user_id is null, new.create_time, old.watch_date_first)
from (select user_id,
             course_id,
             course_name,
             subject_id,
             subject_name,
             category_id,
             category_name,
             watch_date_first
      from ${APP}.dws_course_user_watch_course_td
      where dt = date_add('$do_date', -1)) old
full outer join
     (select dwd.user_id user_id,
             dwd.course_id course_id,
             dim.course_name course_name,
             dim.subject_id subject_id,
             dim.subject_name subject_name,
             dim.category_id category_id,
             dim.category_name category_name,
             dwd.create_time
      from (select d1.user_id     user_id,
                   d1.course_id   course_id,
                   d1.subject_id  subject_id,
                   d1.category_id category_id,
                   d1.create_time create_time
            from ${APP}.dwd_course_audition_inc d1
                     join (select user_id, min(create_time) min_time from ${APP}.dwd_course_audition_inc group by user_id) d2
                          on d1.user_id = d2.user_id
            where d1.create_time = d2.min_time) dwd
               join ${APP}.dim_course_info_full dim on dwd.course_id = dim.id
      where dt='$do_date'
)new
on old.user_id=new.user_id;
"
dws_trade_user_order_td="
insert overwrite table ${APP}.dws_trade_user_order_td partition(dt='$do_date')
select
    NVL(oto.user_id, nto.user_id),
    IF(oto.user_id IS NOT NULL, oto.order_date_first, nto.order_date_first) as order_date_first,
    IF(nto.user_id IS NULL, oto.order_date_last, nto.order_date_last) as order_date_last,
    NVL(oto.order_count_td, 0) + NVL(nto.order_count_td, 0) as order_count_td,
    NVL(oto.amount_td, 0) + NVL(nto.amount_td, 0) as amount_td,
    IF(oto.user_id IS NOT NULL, oto.payment_date_first, nto.payment_date_first) as payment_date_first
from (
    SELECT
        user_id,
        order_date_first,
        order_date_last,
        order_count_td,
        amount_td,
        payment_date_first
    FROM ${APP}.dws_trade_user_order_td
    WHERE dt=date_add('$do_date',-1)
) as oto FULL OUTER JOIN (
    select
        user_id,
        min(order_c_time) as order_date_first,
        max(order_c_time) as order_date_last,
        count(final_amount) as order_count_td,
        sum(final_amount) as amount_td,
        min(payment_date) as payment_date_first
    from (
        select
            order_id,
            user_id,
            final_amount,
            dt as order_c_time
        from ${APP}.dwd_trade_order_info_inc
        where dt='$do_date' and final_amount is not null
    ) toi left join(
        select
            data.order_id,
            min(substr(data.callback_time,0,10)) as payment_date
        from ${APP}.ods_payment_info_inc
        where data.payment_status='1602' and data.total_amount IS NOT NULL
        group by data.order_id
    ) opi on toi.order_id=opi.order_id
    group by user_id
) as nto;
"

dws_user_user_login_td="
insert overwrite table ${APP}.dws_user_user_login_td partition (dt = '$do_date')
select old.user_id,
       nvl(login_date_last, date_format(create_time, 'yyyy-MM-dd')),
       nvl(login_time_last_new,login_date_last),
       (old.login_count_td + new.login_count_1d) login_count_td
from ((
    select user_id,
           login_date_last,
           login_count_td
    from ${APP}.dws_user_user_login_td
    where dt = date_add('$do_date', -1)
) old
    full outer join
    (
        select user_id,
               max(login_time) login_time_last_new,
               count(*) login_count_1d
        from ${APP}.dwd_user_login_inc
        where dt = '$do_date'
        group by user_id
    ) new
    on old.user_id = new.user_id)
         left join (select id, create_time
                    from ${APP}.dim_user_zip
                    where dt = '9999-12-31') t2
                   on old.user_id = t2.id;
"

case $1 in
    "dws_course_user_watch_course_td" )
        hive -e "$dws_course_user_watch_course_td"
    ;;
    "dws_trade_user_order_td" )
        hive -e "$dws_trade_user_order_td"
    ;; 
    "dws_user_user_login_td" )
        hive -e "$dws_user_user_login_td"
    ;; 
    "all" )
        hive -e "$dws_course_user_watch_course_td$dws_trade_user_order_td$dws_user_user_login_td"
    ;;
esac
